/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db_loader;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONTokener;

/**
 *
 * @author ferru001
 */
public class Db_loader
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args)
    {
        if (args.length != 4)
        {
            System.out.println("Usage: java DB_loader <dbhost>:<dbport> <dbusername> <dbpassword> <dbname>");
            System.exit(-1);
        }
        String dbhost = args[0];
        String dbuser = args[1];
        String dbpass = args[2];
        String dbname = args[3];
        
        Pattern p = Pattern.compile("^(.*)\\s+(\\d+)$");
        //fill the dvd table
        BufferedReader r = null;
        InputStream movielistis = Thread.currentThread().getContextClassLoader().getResourceAsStream("movielist.txt");
        System.out.println("Loading Movie Information...");
        r = new BufferedReader(new InputStreamReader(movielistis));
        String line = null;
        Connection conn = null;
        Statement stmt = null;
        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://" + dbhost + "/" + dbname + "?user=" + dbuser + "&password=" + dbpass + "&autoReconnect=true");
            stmt = conn.createStatement();
            stmt.execute("delete from dvd");
            while ((line = r.readLine()) != null)
            {
                line = line.trim();
                Matcher m = p.matcher(line);
                if (m.find())
                {
                    String title = m.group(1).trim();
                    String year = m.group(2).trim();
                    System.out.println(title);
                    String jsonReturn = sendGetRequest("http://www.imdbapi.com/", "t=" + URLEncoder.encode(title) + "&y=" + URLEncoder.encode(year));
                    try
                    {
                        JSONObject jsonObj = new JSONObject(new JSONTokener(jsonReturn));
                        //insert DVD info into dvd table
                        
                        String sql = "insert into dvd (title,genre,actors,image,year,director,runtime) values ('" + escapeString(jsonObj.getString("Title")) + "','" + escapeString(jsonObj.getString("Genre")) + "','" + escapeString(jsonObj.getString("Actors")) + "','" + escapeString(jsonObj.getString("Poster")) + "','" + escapeString(jsonObj.getString("Year")) + "','" + escapeString(jsonObj.getString("Director")) + "','" + escapeString(jsonObj.getString("Runtime")) + "')";
                        System.out.println("SQL: " + sql);
                        stmt.execute(sql);
                    }
                    catch (JSONException ex)
                    {
                        ex.printStackTrace();
                    }
                }
            }
            System.out.println("Finished loading DVD information");
        }
        catch (IOException ex)
        {
            ex.printStackTrace();
            System.exit(-1);
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            System.exit(-1);
        }
        finally
        {
            if (stmt != null)
            {
                try
                {
                    stmt.close();
                }
                catch (SQLException ex)
                {
                    ex.printStackTrace();
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ex)
                {
                    ex.printStackTrace();
                }
            }
        }
    }
    
    static public String escapeString ( String s ) {
        if(s ==null){
            return s;
        }
        String retvalue = s;
        if ( s.indexOf ( "'" ) != -1 || s.indexOf ( "\\" ) != -1 ) {
            StringBuffer hold = new StringBuffer ();
            char c;
            for ( int i = 0; i < s.length (); i ++ ) {
                if ( ( c = s.charAt ( i ) ) == '\'' ) {
                    hold.append ( "''" );
                }
                else if ( ( c = s.charAt ( i ) ) == '\\' ) {
                    hold.append ( "\\\\" );
                }
                else {
                    hold.append ( c );
                }
            }
            retvalue = hold.toString ();
        }
        return retvalue;
    }

    public static String sendGetRequest(String endpoint, String requestParameters)
    {
        String result = null;
        if (endpoint.startsWith("http://"))
        {
            // Send a GET request to the servlet
            try
            {
                // Construct data
                StringBuffer data = new StringBuffer();

                // Send data
                String urlStr = endpoint;
                if (requestParameters != null && requestParameters.length() > 0)
                {
                    urlStr += "?" + requestParameters;
                }
                URL url = new URL(urlStr);
                URLConnection conn = url.openConnection();

                // Get the response
                BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
                StringBuffer sb = new StringBuffer();
                String line;
                while ((line = rd.readLine()) != null)
                {
                    sb.append(line);
                }
                rd.close();
                result = sb.toString();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        return result;
    }
}
